﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Data.Linq;
using System.Data.Common;
using System.Reflection;
using System.IO;
using System.Runtime.CompilerServices;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;
using System.ComponentModel;

public static partial class LinqToSqlExtension
{
    public static void BulkInsert<T>(this IList<T> list, string connection)
    {
        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.BatchSize = list.Count;
            bulkCopy.DestinationTableName = typeof(T).Name;

            var table = new DataTable();
            var props = TypeDescriptor.GetProperties(typeof(T))
                //Dirty hack to make sure we only have system data types 
                //i.e. filter out the relationships/collections
                                       .Cast<PropertyDescriptor>()
                                       .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                                       .ToArray();

            foreach (var propertyInfo in props)
            {
                bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
            }

            var values = new object[props.Length];
            foreach (var item in list)
            {
                for (var i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }

                table.Rows.Add(values);
            }

            bulkCopy.WriteToServer(table);
        }
    }

    /// <summary>
    /// Creates a *.csv file from an IQueryable query, dumping out the 'simple' properties/fields.
    /// </summary>
    /// <param name="query">Represents a SELECT query to execute.</param>
    /// <param name="fileName">The name of the file to create.</param>
    /// <remarks>
    /// <para>If the file specified by <paramref name="fileName"/> exists, it will be deleted.</para>
    /// <para>If the <paramref name="query"/> contains any properties that are entity sets (i.e. rows from a FK relationship) the values will not be dumped to the file.</para>
    /// <para>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</para>
    /// </remarks>
    public static void DumpCSV(this IQueryable query, string fileName)
    {
        query.DumpCSV(fileName, true);
    }

    /// <summary>
    /// Creates a *.csv file from an IQueryable query, dumping out the 'simple' properties/fields.
    /// </summary>
    /// <param name="query">Represents a SELECT query to execute.</param>
    /// <param name="fileName">The name of the file to create.</param>
    /// <param name="deleteFile">Whether or not to delete the file specified by <paramref name="fileName"/> if it exists.</param>
    /// <remarks>
    /// <para>If the <paramref name="query"/> contains any properties that are entity sets (i.e. rows from a FK relationship) the values will not be dumped to the file.</para>
    /// <para>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</para>
    /// </remarks>
    public static void DumpCSV(this IQueryable query, string fileName, bool deleteFile)
    {
        if (File.Exists(fileName) && deleteFile)
        {
            File.Delete(fileName);
        }

        using (var output = new FileStream(fileName, FileMode.CreateNew))
        {
            using (var writer = new StreamWriter(output))
            {
                var firstRow = true;

                PropertyInfo[] properties = null;
                FieldInfo[] fields = null;
                Type type = null;
                bool typeIsAnonymous = false;

                foreach (var r in query)
                {
                    if (type == null)
                    {
                        type = r.GetType();
                        typeIsAnonymous = type.IsAnonymous();
                        properties = type.GetProperties();
                        fields = type.GetFields();
                    }

                    var firstCol = true;

                    if (typeIsAnonymous)
                    {
                        if (firstRow)
                        {
                            foreach (var p in properties)
                            {
                                if (!firstCol) writer.Write(",");
                                else { firstCol = false; }

                                writer.Write(p.Name);
                            }
                            writer.WriteLine();
                        }
                        firstRow = false;
                        firstCol = true;

                        foreach (var p in properties)
                        {
                            if (!firstCol) writer.Write(",");
                            else { firstCol = false; }
                            DumpValue(p.GetValue(r, null), writer);
                        }
                    }
                    else
                    {
                        if (firstRow)
                        {
                            foreach (var p in fields)
                            {
                                if (!firstCol) writer.Write(",");
                                else { firstCol = false; }

                                writer.Write(p.Name);
                            }
                            writer.WriteLine();
                        }
                        firstRow = false;
                        firstCol = true;

                        foreach (var p in fields)
                        {
                            if (!firstCol) writer.Write(",");
                            else { firstCol = false; }

                            DumpValue(p.GetValue(r), writer);
                        }
                    }

                    writer.WriteLine();
                }
            }
        }
    }

    private static void DumpValue(object v, StreamWriter writer)
    {
        if (v != null)
        {
            switch (Type.GetTypeCode(v.GetType()))
            {
                // csv encode the value
                case TypeCode.String:
                    string value = (string)v;
                    if (value.Contains(",") || value.Contains('"') || value.Contains("\n"))
                    {
                        value = value.Replace("\"", "\"\"");

                        if (value.Length > 31735)
                        {
                            value = value.Substring(0, 31732) + "...";
                        }
                        writer.Write("\"" + value + "\"");
                    }
                    else
                    {
                        writer.Write(value);
                    }
                    break;

                default: writer.Write(v); break;
            }
        }
    }

    private static bool IsAnonymous(this Type type)
    {
        if (type == null)
            throw new ArgumentNullException("type");

        // HACK: The only way to detect anonymous types right now.
        return Attribute.IsDefined(type, typeof(CompilerGeneratedAttribute), false)
                   && type.IsGenericType && type.Name.Contains("AnonymousType")
                   && (type.Name.StartsWith("<>") || type.Name.StartsWith("VB$"))
                   && (type.Attributes & TypeAttributes.NotPublic) == TypeAttributes.NotPublic;

    }

    /// <summary>
    /// Batches together multiple IQueryable queries into a single DbCommand and returns all data in
    /// a single roundtrip to the database.
    /// </summary>
    /// <param name="context">The DataContext to execute the batch select against.</param>
    /// <param name="queries">Represents a collections of SELECT queries to execute.</param>
    /// <returns>Returns an IMultipleResults object containing all results.</returns>
    public static IMultipleResults SelectMutlipleResults(this DataContext context, IQueryable[] queries)
    {
        var commandList = new List<DbCommand>();

        foreach (IQueryable query in queries)
        {
            var command = context.GetCommand(query);
            commandList.Add(command);
        }

        SqlCommand batchCommand = CombineCommands(commandList);
        batchCommand.Connection = context.Connection as SqlConnection;

        DbDataReader dr = null;

        if (batchCommand.Connection.State == ConnectionState.Closed)
        {
            batchCommand.Connection.Open();
            dr = batchCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        else
        {
            dr = batchCommand.ExecuteReader();
        }

        IMultipleResults mr = context.Translate(dr);
        return mr;
    }

    /// <summary>
    /// Combines multiple SELECT commands into a single SqlCommand so that all statements can be executed in a 
    /// single roundtrip to the database and return multiple result sets.
    /// </summary>
    /// <param name="commandList">Represents a collection of commands to be batched together.</param>
    /// <returns>Returns a single SqlCommand that executes all SELECT statements at once.</returns>
    private static SqlCommand CombineCommands(List<DbCommand> selectCommands)
    {
        SqlCommand batchCommand = new SqlCommand();
        SqlParameterCollection newParamList = batchCommand.Parameters;

        int commandCount = 0;

        foreach (DbCommand cmd in selectCommands)
        {
            string commandText = cmd.CommandText;
            DbParameterCollection paramList = cmd.Parameters;
            int paramCount = paramList.Count;

            for (int currentParam = paramCount - 1; currentParam >= 0; currentParam--)
            {
                DbParameter param = paramList[currentParam];
                DbParameter newParam = CloneParameter(param);
                string newParamName = param.ParameterName.Replace("@", string.Format("@{0}_", commandCount));
                commandText = commandText.Replace(param.ParameterName, newParamName);
                newParam.ParameterName = newParamName;
                newParamList.Add(newParam);
            }
            if (batchCommand.CommandText.Length > 0)
            {
                batchCommand.CommandText += ";";
            }
            batchCommand.CommandText += commandText;
            commandCount++;
        }

        return batchCommand;
    }

    /// <summary>
    /// Returns a clone (via copying all properties) of an existing DbParameter.
    /// </summary>
    /// <param name="src">The DbParameter to clone.</param>
    /// <returns>Returns a clone (via copying all properties) of an existing DbParameter.</returns>
    private static DbParameter CloneParameter(DbParameter src)
    {
        SqlParameter source = (SqlParameter)src;
        SqlParameter destination = new SqlParameter();

        destination.Value = source.Value;
        destination.Direction = source.Direction;
        destination.Size = source.Size;
        destination.Offset = source.Offset;
        destination.SourceColumn = source.SourceColumn;
        destination.SourceVersion = source.SourceVersion;
        destination.SourceColumnNullMapping = source.SourceColumnNullMapping;
        destination.IsNullable = source.IsNullable;

        destination.CompareInfo = source.CompareInfo;
        destination.XmlSchemaCollectionDatabase = source.XmlSchemaCollectionDatabase;
        destination.XmlSchemaCollectionOwningSchema = source.XmlSchemaCollectionOwningSchema;
        destination.XmlSchemaCollectionName = source.XmlSchemaCollectionName;
        destination.UdtTypeName = source.UdtTypeName;
        destination.TypeName = source.TypeName;
        destination.ParameterName = source.ParameterName;
        destination.Precision = source.Precision;
        destination.Scale = source.Scale;

        return destination;
    }

    /// <summary>
    /// Immediately deletes all entities from the collection with a single delete command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
    /// <typeparam name="TPrimaryKey">Represents the object type for the primary key of rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="primaryKey">Represents the primary key of the item to be removed from <paramref name="table"/>.</param>
    /// <returns>The number of rows deleted from the database (maximum of 1).</returns>
    /// <remarks>
    /// <para>If the primary key for <paramref name="table"/> is a composite key, <paramref name="primaryKey"/> should be an anonymous type with property names mapping to the property names of objects of type <typeparamref name="TEntity"/>.</para>
    /// </remarks>
    public static int DeleteByPK<TEntity>(this Table<TEntity> table, object primaryKey) where TEntity : class
    {
        DbCommand delete = table.GetDeleteByPKCommand<TEntity>(primaryKey);

        var parameters = from p in delete.Parameters.Cast<DbParameter>()
                         select p.Value;

        return table.Context.ExecuteCommand(delete.CommandText, parameters.ToArray());
    }
    /// <summary>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete a entity row via the supplied primary key.</summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
    /// <typeparam name="TPrimaryKey">Represents the object type for the primary key of rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="primaryKey">Represents the primary key of the item to be removed from <paramref name="table"/>.</param>
    /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete a entity row via the supplied primary key.</returns>
    /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
    public static string DeleteByPKPreview<TEntity>(this Table<TEntity> table, object primaryKey) where TEntity : class
    {
        DbCommand delete = table.GetDeleteByPKCommand<TEntity>(primaryKey);
        return delete.PreviewCommandText(false) + table.Context.GetLog();
    }

    private static DbCommand GetDeleteByPKCommand<TEntity>(this Table<TEntity> table, object primaryKey) where TEntity : class
    {
        Type type = primaryKey.GetType();
        bool typeIsAnonymous = type.IsAnonymous();
        string dbName = table.GetDbName();

        var metaTable = table.Context.Mapping.GetTable(typeof(TEntity));

        var keys = from mdm in metaTable.RowType.DataMembers
                   where mdm.IsPrimaryKey
                   select new { mdm.MappedName, mdm.Name, mdm.Type };

        SqlCommand deleteCommand = new SqlCommand();
        deleteCommand.Connection = table.Context.Connection as SqlConnection;

        var whereSB = new StringBuilder();

        foreach (var key in keys)
        {
            // Add new parameter with massaged name to avoid clashes.
            whereSB.AppendFormat("[{0}] = @p{1}, ", key.MappedName, deleteCommand.Parameters.Count);

            object value = primaryKey;
            if (typeIsAnonymous || (type.IsClass && type != typeof(string)))
            {
                if (typeIsAnonymous)
                {
                    PropertyInfo property = type.GetProperty(key.Name);

                    if (property == null)
                    {
                        throw new ArgumentOutOfRangeException(string.Format("The property {0} which is defined as part of the primary key for {1} was not supplied by the parameter primaryKey.", key.Name, metaTable.TableName));
                    }

                    value = property.GetValue(primaryKey, null);
                }
                else
                {
                    FieldInfo field = type.GetField(key.Name);

                    if (field == null)
                    {
                        throw new ArgumentOutOfRangeException(string.Format("The property {0} which is defined as part of the primary key for {1} was not supplied by the parameter primaryKey.", key.Name, metaTable.TableName));
                    }

                    value = field.GetValue(primaryKey);
                }

                if (value.GetType() != key.Type)
                {
                    throw new InvalidCastException(string.Format("The property {0} ({1}) does not have the same type as {2} ({3}).", key.Name, value.GetType(), key.MappedName, key.Type));
                }
            }
            else if (value.GetType() != key.Type)
            {
                throw new InvalidCastException(string.Format("The value supplied in primaryKey ({0}) does not have the same type as {1} ({2}).", value.GetType(), key.MappedName, key.Type));
            }

            deleteCommand.Parameters.Add(new SqlParameter(string.Format("@p{0}", deleteCommand.Parameters.Count), value));
        }

        string wherePK = whereSB.ToString();

        if (wherePK == "")
        {
            throw new MissingPrimaryKeyException(string.Format("{0} does not have a primary key defined.  Batch updating/deleting can not be used for tables without a primary key.", metaTable.TableName));
        }

        deleteCommand.CommandText = string.Format("DELETE {0}\r\nWHERE {1}", dbName, wherePK.Substring(0, wherePK.Length - 2));

        return deleteCommand;
    }

    /// <summary>
    /// Immediately deletes all entities from the collection with a single delete command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
    /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
    /// <returns>The number of rows deleted from the database.</returns>
    /// <remarks>
    /// <para>Similiar to stored procedures, and opposite from DeleteAllOnSubmit, rows provided in <paramref name="entities"/> will be deleted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
    /// <para>Additionally, to improve performance, instead of creating a delete command for each item in <paramref name="entities"/>, a single delete command is created.</para>
    /// </remarks>
    public static int DeleteBatch<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
    {
        DbCommand delete = table.GetDeleteBatchCommand<TEntity>(entities);

        var parameters = from p in delete.Parameters.Cast<DbParameter>()
                         select p.Value;

        return table.Context.ExecuteCommand(delete.CommandText, parameters.ToArray());
    }

    /// <summary>
    /// Immediately deletes all entities from the collection with a single delete command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
    /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
    /// <returns>The number of rows deleted from the database.</returns>
    /// <remarks>
    /// <para>Similiar to stored procedures, and opposite from DeleteAllOnSubmit, rows provided in <paramref name="entities"/> will be deleted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
    /// <para>Additionally, to improve performance, instead of creating a delete command for each item in <paramref name="entities"/>, a single delete command is created.</para>
    /// </remarks>
    public static int DeleteBatch<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter) where TEntity : class
    {
        return table.DeleteBatch(table.Where(filter));
    }

    /// <summary>
    /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
    /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
    /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
    /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
    public static string DeleteBatchPreview<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
    {
        DbCommand delete = table.GetDeleteBatchCommand<TEntity>(entities);
        return delete.PreviewCommandText(false) + table.Context.GetLog();
    }

    /// <summary>
    /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
    /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
    /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
    /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
    public static string DeleteBatchPreview<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter) where TEntity : class
    {
        return table.DeleteBatchPreview(table.Where(filter));
    }

    /// <summary>
    /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
    /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
    /// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
    /// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks>
    public static string DeleteBatchSQL<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
    {
        DbCommand delete = table.GetDeleteBatchCommand<TEntity>(entities);
        return delete.PreviewCommandText(true);
    }

    /// <summary>
    /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
    /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
    /// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
    /// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks>
    public static string DeleteBatchSQL<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter) where TEntity : class
    {
        return table.DeleteBatchSQL(table.Where(filter));
    }

    /// <summary>
    /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
    /// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param>
    /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
    /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns>
    /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
    public static string UpdateBatchPreview<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
    {
        DbCommand update = table.GetUpdateBatchCommand<TEntity>(entities, evaluator);
        return update.PreviewCommandText(false) + table.Context.GetLog();
    }

    /// <summary>
    /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
    /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
    /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
    /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns>
    /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
    public static string UpdateBatchPreview<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
    {
        return table.UpdateBatchPreview(table.Where(filter), evaluator);
    }

    /// <summary>
    /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
    /// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param>
    /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
    /// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns>
    /// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks>
    public static string UpdateBatchSQL<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
    {
        DbCommand update = table.GetUpdateBatchCommand<TEntity>(entities, evaluator);
        return update.PreviewCommandText(true);
    }

    /// <summary>
    /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
    /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
    /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
    /// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns>
    /// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks>
    public static string UpdateBatchSQL<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
    {
        return table.UpdateBatchSQL(table.Where(filter), evaluator);
    }

    /// <summary>
    /// Immediately updates all entities in the collection with a single update command based on a <typeparamref name="TEntity"/> created from a Lambda expression.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
    /// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param>
    /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
    /// <returns>The number of rows updated in the database.</returns>
    /// <remarks>
    /// <para>Similiar to stored procedures, and opposite from similiar InsertAllOnSubmit, rows provided in <paramref name="entities"/> will be updated immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
    /// <para>Additionally, to improve performance, instead of creating an update command for each item in <paramref name="entities"/>, a single update command is created.</para>
    /// </remarks>
    public static int UpdateBatch<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
    {
        DbCommand update = table.GetUpdateBatchCommand<TEntity>(entities, evaluator);

        var parameters = from p in update.Parameters.Cast<DbParameter>()
                         select p.Value;
        return table.Context.ExecuteCommand(update.CommandText, parameters.ToArray());
    }

    /// <summary>
    /// Immediately updates all entities in the collection with a single update command based on a <typeparamref name="TEntity"/> created from a Lambda expression.
    /// </summary>
    /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
    /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param>
    /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param>
    /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param>
    /// <returns>The number of rows updated in the database.</returns>
    /// <remarks>
    /// <para>Similiar to stored procedures, and opposite from similiar InsertAllOnSubmit, rows provided in <paramref name="entities"/> will be updated immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
    /// <para>Additionally, to improve performance, instead of creating an update command for each item in <paramref name="entities"/>, a single update command is created.</para>
    /// </remarks>
    public static int UpdateBatch<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
    {
        return table.UpdateBatch(table.Where(filter), evaluator);
    }

    /// <summary>
    /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to perform the query's select statement.
    /// </summary>
    /// <param name="context">The DataContext to execute the batch select against.</param>
    /// <param name="query">Represents the SELECT query to execute.</param>
    /// <returns>Returns the Transact SQL string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns>
    /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
    public static string PreviewSQL(this DataContext context, IQueryable query)
    {
        var cmd = context.GetCommand(query);
        return cmd.PreviewCommandText(true);
    }

    /// <summary>
    /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to perform the query's select statement.
    /// </summary>
    /// <param name="context">The DataContext to execute the batch select against.</param>
    /// <param name="query">Represents the SELECT query to execute.</param>
    /// <returns>Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns>
    /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
    public static string PreviewCommandText(this DataContext context, IQueryable query)
    {
        var cmd = context.GetCommand(query);
        return cmd.PreviewCommandText(false);
    }

    /// <summary>
    /// Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.
    /// </summary>
    /// <param name="cmd">The <see cref="DbCommand"/> to analyze.</param>
    /// <param name="forTransactSQL">Whether or not the text should be formatted as 'logging' similiar to LINQ to SQL output, or in valid Transact SQL syntax ready for use with a 'query analyzer' type tool.</param>
    /// <returns>Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns>
    /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
    private static string PreviewCommandText(this DbCommand cmd, bool forTransactSQL)
    {
        var output = new StringBuilder();

        if (!forTransactSQL) output.AppendLine(cmd.CommandText);

        foreach (DbParameter parameter in cmd.Parameters)
        {
            int num = 0;
            int num2 = 0;
            PropertyInfo property = parameter.GetType().GetProperty("Precision");
            if (property != null)
            {
                num = (int)Convert.ChangeType(property.GetValue(parameter, null), typeof(int), CultureInfo.InvariantCulture);
            }
            PropertyInfo info2 = parameter.GetType().GetProperty("Scale");
            if (info2 != null)
            {
                num2 = (int)Convert.ChangeType(info2.GetValue(parameter, null), typeof(int), CultureInfo.InvariantCulture);
            }
            SqlParameter parameter2 = parameter as SqlParameter;

            if (forTransactSQL)
            {
                output.AppendFormat("DECLARE {0} {1}{2}; SET {0} = {3}\r\n",
                    new object[] { 
								parameter.ParameterName, 
								( parameter2 == null ) ? parameter.DbType.ToString() : parameter2.SqlDbType.ToString(), 
								( parameter.Size > 0 ) ? "( " + parameter.Size.ToString( CultureInfo.CurrentCulture ) + " )" : "", 
								GetParameterTransactValue( parameter, parameter2 ) });
            }
            else
            {
                output.AppendFormat("-- {0}: {1} {2} (Size = {3}; Prec = {4}; Scale = {5}) [{6}]\r\n", new object[] { parameter.ParameterName, parameter.Direction, (parameter2 == null) ? parameter.DbType.ToString() : parameter2.SqlDbType.ToString(), parameter.Size.ToString(CultureInfo.CurrentCulture), num, num2, (parameter2 == null) ? parameter.Value : parameter2.SqlValue });
            }
        }

        if (forTransactSQL) output.Append("\r\n" + cmd.CommandText);

        return output.ToString();
    }

    private static string GetParameterTransactValue(DbParameter parameter, SqlParameter parameter2)
    {
        if (parameter2 == null) return parameter.Value.ToString(); // Not going to deal with NON SQL parameters.

        switch (parameter2.SqlDbType)
        {
            case SqlDbType.Char:
            case SqlDbType.Date:
            case SqlDbType.DateTime:
            case SqlDbType.DateTime2:
            case SqlDbType.NChar:
            case SqlDbType.NText:
            case SqlDbType.NVarChar:
            case SqlDbType.SmallDateTime:
            case SqlDbType.Text:
            case SqlDbType.VarChar:
            case SqlDbType.UniqueIdentifier:
                return string.Format("'{0}'", parameter2.SqlValue);

            default:
                return parameter2.SqlValue.ToString();
        }
    }

    private static DbCommand GetDeleteBatchCommand<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
    {
        var deleteCommand = table.Context.GetCommand(entities);
        deleteCommand.CommandText = string.Format("DELETE {0}\r\n", table.GetDbName()) + GetBatchJoinQuery<TEntity>(table, entities);
        return deleteCommand;
    }

    private static DbCommand GetUpdateBatchCommand<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
    {
        var updateCommand = table.Context.GetCommand(entities);

        var setSB = new StringBuilder();
        int memberInitCount = 1;

        // Process the MemberInitExpression (there should only be one in the evaluator Lambda) to convert the expression tree
        // into a valid DbCommand.  The Visit<> method will only process expressions of type MemberInitExpression and requires
        // that a MemberInitExpression be returned - in our case we'll return the same one we are passed since we are building
        // a DbCommand and not 'really using' the evaluator Lambda.
        evaluator.Visit<MemberInitExpression>(delegate(MemberInitExpression expression)
        {
            if (memberInitCount > 1)
            {
                throw new NotImplementedException("Currently only one MemberInitExpression is allowed for the evaluator parameter.");
            }
            memberInitCount++;

            setSB.Append(GetDbSetStatement<TEntity>(expression, table, updateCommand));

            return expression; // just return passed in expression to keep 'visitor' happy.
        });

        // Complete the command text by concatenating bits together.
        updateCommand.CommandText = string.Format("UPDATE {0}\r\n{1}\r\n\r\n{2}",
                                                        table.GetDbName(),									// Database table name
                                                        setSB.ToString(),									// SET fld = {}, fld2 = {}, ...
                                                        GetBatchJoinQuery<TEntity>(table, entities));	// Subquery join created from entities command text

        if (updateCommand.CommandText.IndexOf("[arg0]") >= 0 || updateCommand.CommandText.IndexOf("NULL AS [EMPTY]") >= 0)
        {
            // TODO (Chris): Probably a better way to determine this by using an visitor on the expression before the
            //				 var selectExpression = Expression.Call... method call (search for that) and see which funcitons
            //				 are being used and determine if supported by LINQ to SQL
            throw new NotSupportedException(string.Format("The evaluator Expression<Func<{0},{0}>> has processing that needs to be performed once the query is returned (i.e. string.Format()) and therefore can not be used during batch updating.", table.GetType()));
        }

        return updateCommand;
    }

    private static string GetDbSetStatement<TEntity>(MemberInitExpression memberInitExpression, Table<TEntity> table, DbCommand updateCommand) where TEntity : class
    {
        var entityType = typeof(TEntity);

        if (memberInitExpression.Type != entityType)
        {
            throw new NotImplementedException(string.Format("The MemberInitExpression is intializing a class of the incorrect type '{0}' and it should be '{1}'.", memberInitExpression.Type, entityType));
        }

        var setSB = new StringBuilder();

        var tableName = table.GetDbName();
        var metaTable = table.Context.Mapping.GetTable(entityType);
        // Used to look up actual field names when MemberAssignment is a constant,
        // need both the Name (matches the property name on LINQ object) and the
        // MappedName (db field name).
        var dbCols = from mdm in metaTable.RowType.DataMembers
                     select new { mdm.MappedName, mdm.Name };

        // Walk all the expression bindings and generate SQL 'commands' from them.  Each binding represents a property assignment
        // on the TEntity object initializer Lambda expression.
        foreach (var binding in memberInitExpression.Bindings)
        {
            var assignment = binding as MemberAssignment;

            if (binding == null)
            {
                throw new NotImplementedException("All bindings inside the MemberInitExpression are expected to be of type MemberAssignment.");
            }

            // TODO (Document): What is this doing?  I know it's grabbing existing parameter to pass into Expression.Call() but explain 'why'
            //					I assume it has something to do with fact we can't just access the parameters of assignment.Expression?
            //					Also, any concerns of whether or not if there are two params of type entity type?
            ParameterExpression entityParam = null;
            assignment.Expression.Visit<ParameterExpression>(delegate(ParameterExpression p) { if (p.Type == entityType) entityParam = p; return p; });

            // Get the real database field name.  binding.Member.Name is the 'property' name of the LINQ object
            // so I match that to the Name property of the table mapping DataMembers.
            string name = binding.Member.Name;
            var dbCol = (from c in dbCols
                         where c.Name == name
                         select c).FirstOrDefault();

            if (dbCol == null)
            {
                throw new ArgumentOutOfRangeException(name, string.Format("The corresponding field on the {0} table could not be found.", tableName));
            }

            // If entityParam is NULL, then no references to other columns on the TEntity row and need to eval 'constant' value...
            if (entityParam == null)
            {
                // Compile and invoke the assignment expression to obtain the contant value to add as a parameter.
                var constant = Expression.Lambda(assignment.Expression, null).Compile().DynamicInvoke();

                // use the MappedName from the table mapping DataMembers - that is field name in DB table.
                if (constant == null)
                {
                    setSB.AppendFormat("[{0}] = null, ", dbCol.MappedName);
                }
                else
                {
                    // Add new parameter with massaged name to avoid clashes.
                    setSB.AppendFormat("[{0}] = @p{1}, ", dbCol.MappedName, updateCommand.Parameters.Count);
                    updateCommand.Parameters.Add(new SqlParameter(string.Format("@p{0}", updateCommand.Parameters.Count), constant));
                }
            }
            else
            {
                // TODO (Documentation): Explain what we are doing here again, I remember you telling me why we have to call but I can't remember now.
                // Wny are we calling Expression.Call and what are we passing it?  Below comments are just 'made up' and probably wrong.

                // Create a MethodCallExpression which represents a 'simple' select of *only* the assignment part (right hand operator) of
                // of the MemberInitExpression.MemberAssignment so that we can let the Linq Provider do all the 'sql syntax' generation for
                // us. 
                //
                // For Example: TEntity.Property1 = TEntity.Property1 + " Hello"
                // This selectExpression will be only dealing with TEntity.Property1 + " Hello"
                var selectExpression = Expression.Call(
                                            typeof(Queryable),
                                            "Select",
                                            new Type[] { entityType, assignment.Expression.Type },

                // TODO (Documentation): How do we know there are only 'two' parameters?  And what is Expression.Lambda
                    //						 doing?  I assume it's returning a type of assignment.Expression.Type to match above?

                                            Expression.Constant(table),
                                            Expression.Lambda(assignment.Expression, entityParam));

                setSB.AppendFormat("[{0}] = {1}, ",
                                        dbCol.MappedName,
                                        GetDbSetAssignment(table, selectExpression, updateCommand, name));
            }
        }

        var setStatements = setSB.ToString();
        return "SET " + setStatements.Substring(0, setStatements.Length - 2); // remove ', '
    }

    /// <summary>
    /// Some LINQ Query syntax is invalid because SQL (or whomever the provider is) can not translate it to its native language.  
    /// DataContext.GetCommand() does not detect this, only IProvider.Execute or IProvider.Compile call the necessary code to 
    /// check this.  This function invokes the IProvider.Compile to make sure the provider can translate the expression.
    /// </summary>
    /// <remarks>
    /// An example of a LINQ query that previously 'worked' in the *Batch methods but needs to throw an exception is something
    /// like the following:
    /// 
    /// var pay = 
    ///		from h in HistoryData
    ///		where h.his.Groups.gName == "Ochsner" && h.hisType == "pay"
    ///		select h;
    ///		
    /// HistoryData.UpdateBatchPreview( pay, h => new HistoryData { hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString() } ).Dump();
    /// 
    /// The int.Parse is not valid and needs to throw an exception like: 
    /// 
    ///		Could not translate expression '(Parse(p.hisIndex) - 1).ToString()' into SQL and could not treat it as a local expression.
    ///		
    ///	Unfortunately, the IProvider.Compile is internal and I need to use Reflection to call it (ugh).  I've several e-mails sent into
    ///	MS LINQ team members and am waiting for a response and will correct/improve code as soon as possible.
    /// </remarks>
    private static void ValidateExpression(ITable table, Expression expression)
    {
        var context = table.Context;
        PropertyInfo providerProperty = context.GetType().GetProperty("Provider", BindingFlags.Instance | BindingFlags.NonPublic);
        var provider = providerProperty.GetValue(context, null);
        var compileMI = provider.GetType().GetMethod("System.Data.Linq.Provider.IProvider.Compile", BindingFlags.Instance | BindingFlags.NonPublic);

        // Simply compile the expression to see if it will work.
        compileMI.Invoke(provider, new object[] { expression });
    }

    private static string GetDbSetAssignment(ITable table, MethodCallExpression selectExpression, DbCommand updateCommand, string bindingName)
    {
        ValidateExpression(table, selectExpression);

        // Convert the selectExpression into an IQueryable query so that I can get the CommandText
        var selectQuery = (table as IQueryable).Provider.CreateQuery(selectExpression);

        // Get the DbCommand so I can grab relavent parts of CommandText to construct a field 
        // assignment and based on the 'current TEntity row'.  Additionally need to massage parameter 
        // names from temporary command when adding to the final update command.
        var selectCmd = table.Context.GetCommand(selectQuery);
        var selectStmt = selectCmd.CommandText;
        selectStmt = selectStmt.Substring(7,									// Remove 'SELECT ' from front ( 7 )
                                    selectStmt.IndexOf("\r\nFROM ") - 7)		// Return only the selection field expression
                                .Replace("[t0].", "")							// Remove table alias from the select
                                .Replace(" AS [value]", "")					// If the select is not a direct field (constant or expression), remove the field alias
                                .Replace("@p", "@p" + bindingName);			// Replace parameter name so doesn't conflict with existing ones.

        foreach (var selectParam in selectCmd.Parameters.Cast<DbParameter>())
        {
            var paramName = string.Format("@p{0}", updateCommand.Parameters.Count);

            // DataContext.ExecuteCommand ultimately just takes a object array of parameters and names them p0-N.  
            // So I need to now do replaces on the massaged value to get it in proper format.
            selectStmt = selectStmt.Replace(
                                selectParam.ParameterName.Replace("@p", "@p" + bindingName),
                                paramName);

            updateCommand.Parameters.Add(new SqlParameter(paramName, selectParam.Value));
        }

        return selectStmt;
    }

    private static string GetBatchJoinQuery<TEntity>(Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
    {
        var metaTable = table.Context.Mapping.GetTable(typeof(TEntity));

        var keys = from mdm in metaTable.RowType.DataMembers
                   where mdm.IsPrimaryKey
                   select new { mdm.MappedName };

        var joinSB = new StringBuilder();
        var subSelectSB = new StringBuilder();

        foreach (var key in keys)
        {
            joinSB.AppendFormat("j0.[{0}] = j1.[{0}] AND ", key.MappedName);
            // For now, always assuming table is aliased as t0.  Should probably improve at some point.
            // Just writing a smaller sub-select so it doesn't get all the columns of data, but instead
            // only the primary key fields used for joining.
            subSelectSB.AppendFormat("[t0].[{0}], ", key.MappedName);
        }

        var selectCommand = table.Context.GetCommand(entities);
        var select = selectCommand.CommandText;

        var join = joinSB.ToString();

        if (join == "")
        {
            throw new MissingPrimaryKeyException(string.Format("{0} does not have a primary key defined.  Batch updating/deleting can not be used for tables without a primary key.", metaTable.TableName));
        }

        join = join.Substring(0, join.Length - 5);											// Remove last ' AND '
        #region - Better ExpressionTree Handling Needed -
        /*
			
			Below is a sample query where the let statement was used to simply the 'where clause'.  However, it produced an extra level
			in the query.
			 
			var manage =
				from u in User
				join g in Groups on u.User_Group_id equals g.gKey into groups
				from g in groups.DefaultIfEmpty()
				let correctGroup = groupsToManage.Contains( g.gName ) || ( groupsToManage.Contains( "_GLOBAL" ) && g.gKey == null )
				where correctGroup && ( users.Contains( u.User_Authenticate_id ) || userEmails.Contains( u.User_Email ) ) || userKeys.Contains( u.User_id )
				select u;
			 
			Produces this SQL:
			SELECT [t2].[User_id] AS [uKey], [t2].[User_Authenticate_id] AS [uAuthID], [t2].[User_Email] AS [uEmail], [t2].[User_Pin] AS [uPin], [t2].[User_Active] AS [uActive], [t2].[uAdminAuthID], [t2].[uFailureCount]
			FROM (
				SELECT [t0].[User_id], [t0].[User_Authenticate_id], [t0].[User_Email], [t0].[User_Pin], [t0].[User_Active], [t0].[uFailureCount], [t0].[uAdminAuthID], 
					(CASE 
						WHEN [t1].[gName] IN (@p0) THEN 1
						WHEN NOT ([t1].[gName] IN (@p0)) THEN 0
						ELSE NULL
					 END) AS [value]
				FROM [User] AS [t0]
				LEFT OUTER JOIN [Groups] AS [t1] ON [t0].[User_Group_id] = ([t1].[gKey])
				) AS [t2]
			WHERE (([t2].[value] = 1) AND (([t2].[User_Authenticate_id] IN (@p1)) OR ([t2].[User_Email] IN (@p2)))) OR ([t2].[User_id] IN (@p3))			 
			
			If I put the entire where in one line...
			where 	( groupsToManage.Contains( g.gName ) || ( groupsToManage.Contains( "_GLOBAL" ) && g.gKey == null ) ) && 
					( users.Contains( u.User_Authenticate_id ) || userEmails.Contains( u.User_Email ) ) || userKeys.Contains ( u.User_id )

			I get this SQL:
			SELECT [t0].[User_id] AS [uKey], [t0].[User_Authenticate_id] AS [uAuthID], [t0].[User_Email] AS [uEmail], [t0].[User_Pin] AS [uPin], [t0].[User_Active] AS [uActive], [t0].[uAdminAuthID], [t0].[uFailureCount]
			FROM [User] AS [t0]
			LEFT OUTER JOIN [Groups] AS [t1] ON [t0].[User_Group_id] = ([t1].[gKey])
			WHERE (([t1].[gName] IN (@p0)) AND (([t0].[User_Authenticate_id] IN (@p1)) OR ([t0].[User_Email] IN (@p2)))) OR ([t0].[User_id] IN (@p3))			
			
			The second 'cleaner' SQL worked with my original 'string parsing' of simply looking for [t0] and stripping everything before it
			to get rid of the SELECT and any 'TOP' clause if present.  But the first SQL introduced a layer which caused [t2] to be used.  So
			I have to do a bit different string parsing.  There is probably a more efficient way to examine the ExpressionTree and figure out
			if something like this is going to happen.  I will explore it later.
			*/
        #endregion
        var endSelect = select.IndexOf("[t");													// Get 'SELECT ' and any TOP clause if present
        var selectClause = select.Substring(0, endSelect);
        var selectTableNameStart = endSelect + 1;												// Get the table name LINQ to SQL used in query generation
        var selectTableName = select.Substring(selectTableNameStart,							// because I have to replace [t0] with it in the subSelectSB
                                    select.IndexOf("]", selectTableNameStart) - (selectTableNameStart));

        // TODO (Chris): I think instead of searching for ORDER BY in the entire select statement, I should examine the ExpressionTree and see
        // if the *outer* select (in case there are nested subselects) has an orderby clause applied to it.
        var needsTopClause = selectClause.IndexOf(" TOP ") < 0 && select.IndexOf("\r\nORDER BY ") > 0;

        var subSelect = selectClause
                            + (needsTopClause ? "TOP 100 PERCENT " : "")							// If order by in original select without TOP clause, need TOP
                            + subSelectSB.ToString()												// Append just the primary keys.
                                         .Replace("[t0]", string.Format("[{0}]", selectTableName));
        subSelect = subSelect.Substring(0, subSelect.Length - 2);									// Remove last ', '

        subSelect += select.Substring(select.IndexOf("\r\nFROM ")); // Create a sub SELECT that *only* includes the primary key fields

        var batchJoin = String.Format("FROM {0} AS j0 INNER JOIN (\r\n\r\n{1}\r\n\r\n) AS j1 ON ({2})\r\n", table.GetDbName(), subSelect, join);
        return batchJoin;
    }

    private static string GetDbName<TEntity>(this Table<TEntity> table) where TEntity : class
    {
        var entityType = typeof(TEntity);
        var metaTable = table.Context.Mapping.GetTable(entityType);
        var tableName = metaTable.TableName;

        if (!tableName.StartsWith("["))
        {
            string[] parts = tableName.Split('.');
            tableName = string.Format("[{0}]", string.Join("].[", parts));
        }

        return tableName;
    }

    private static string GetLog(this DataContext context)
    {
        PropertyInfo providerProperty = context.GetType().GetProperty("Provider", BindingFlags.Instance | BindingFlags.NonPublic);
        var provider = providerProperty.GetValue(context, null);
        Type providerType = provider.GetType();

        PropertyInfo modeProperty = providerType.GetProperty("Mode", BindingFlags.Instance | BindingFlags.NonPublic);
        FieldInfo servicesField = providerType.GetField("services", BindingFlags.Instance | BindingFlags.NonPublic);
        object services = servicesField != null ? servicesField.GetValue(provider) : null;
        PropertyInfo modelProperty = services != null ? services.GetType().GetProperty("Model", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.GetProperty) : null;

        return string.Format("-- Context: {0}({1}) Model: {2} Build: {3}\r\n",
                        providerType.Name,
                        modeProperty != null ? modeProperty.GetValue(provider, null) : "unknown",
                        modelProperty != null ? modelProperty.GetValue(services, null).GetType().Name : "unknown",
                        "3.5.21022.8");
    }

    /// <summary>
    /// Returns a list of changed items inside the Context before being applied to the data store.
    /// </summary>
    /// <param name="context">The DataContext to interogate for pending changes.</param>
    /// <returns>Returns a list of changed items inside the Context before being applied to the data store.</returns>
    /// <remarks>Based on Ryan Haney's code at http://dotnetslackers.com/articles/csharp/GettingChangedEntitiesFromLINQToSQLDataContext.aspx.  Note that this code relies on reflection of private fields and members.</remarks>
    public static List<ChangedItems<TEntity>> GetChangedItems<TEntity>(this DataContext context)
    {
        // create a dictionary of type TItem for return to caller
        List<ChangedItems<TEntity>> changedItems = new List<ChangedItems<TEntity>>();

        PropertyInfo providerProperty = context.GetType().GetProperty("Provider", BindingFlags.Instance | BindingFlags.NonPublic);
        var provider = providerProperty.GetValue(context, null);
        Type providerType = provider.GetType();

        // use reflection to get changed items from data context
        object services = providerType.GetField("services",
          BindingFlags.NonPublic |
          BindingFlags.Instance |
          BindingFlags.GetField).GetValue(provider);

        object tracker = services.GetType().GetField("tracker",
          BindingFlags.NonPublic |
          BindingFlags.Instance |
          BindingFlags.GetField).GetValue(services);

        System.Collections.IDictionary trackerItems =
          (System.Collections.IDictionary)tracker.GetType().GetField("items",
          BindingFlags.NonPublic |
          BindingFlags.Instance |
          BindingFlags.GetField).GetValue(tracker);

        // iterate through each item in context, adding
        // only those that are of type TItem to the changedItems dictionary
        foreach (System.Collections.DictionaryEntry entry in trackerItems)
        {
            object original = entry.Value.GetType().GetField("original",
                              BindingFlags.NonPublic |
                              BindingFlags.Instance |
                              BindingFlags.GetField).GetValue(entry.Value);

            if (entry.Key is TEntity && original is TEntity)
            {
                changedItems.Add(
                  new ChangedItems<TEntity>((TEntity)entry.Key, (TEntity)original)
                );
            }
        }
        return changedItems;
    }
}

public class ChangedItems<TEntity>
{
    public ChangedItems(TEntity Current, TEntity Original)
    {
        this.Current = Current;
        this.Original = Original;
    }
    public TEntity Current { get; set; }
    public TEntity Original { get; set; }
}

public static partial class LinqToSqlExtension
{
    public static IQueryable<TSource> Between<TSource, TKey>(
        this IQueryable<TSource> source,
        Expression<Func<TSource, TKey>> keySelector,
        TKey low, TKey high) where TKey : IComparable<TKey>
    {
        Expression key = Expression.Invoke(keySelector,
             keySelector.Parameters.ToArray());
        Expression lowerBound = Expression.LessThanOrEqual
            (Expression.Constant(low), key);
        Expression upperBound = Expression.LessThanOrEqual
            (key, Expression.Constant(high));
        Expression and = Expression.AndAlso(lowerBound, upperBound);
        Expression<Func<TSource, bool>> lambda =
            Expression.Lambda<Func<TSource, bool>>(and, keySelector.Parameters);
        return source.Where(lambda);
    }

    public static IQueryable<TSource> WhereIf<TSource>(
        this IQueryable<TSource> source, bool condition,
        Expression<Func<TSource, bool>> predicate)
    {
        if (condition)
            return source.Where(predicate);
        else
            return source;
    }

    public static IEnumerable<TSource> WhereIf<TSource>(
        this IEnumerable<TSource> source, bool condition,
        Func<TSource, bool> predicate)
    {
        if (condition)
            return source.Where(predicate);
        else
            return source;
    }
}